package com.kexinxin.service.impl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.kexinxin.util.ClassifyUtil;
import com.kexinxin.util.DBUtil;
import com.kexinxin.util.ExcelReader;
import com.kexinxin.util.excelinsert.ExcelContent;


public class InsertDAOImpl {
	public static void insertExcelContent(List<ExcelContent> excelContentList) {
		Map<String, Integer> categoryMap = getCategoryMap();
		Map<String, Integer> skillMap = getSkillMap();
		Map<String, Integer> userMap = getUserMap();
		Connection conn = DBUtil.getConnection();
		PreparedStatement pStatement = null;
		ClassifyUtil classifyUtil = new ClassifyUtil();
		for (int i = 0; i < excelContentList.size(); i++) {
			// int questionId = Math.abs((int) System.currentTimeMillis() %
			// 1000000);
			ExcelContent excelContent = excelContentList.get(i);
			// 假设skill为java
			String skillName = null;
			try {
				skillName = classifyUtil.getBestOneCategory(excelContent.getQuestion_content());
			} catch (ClassNotFoundException e1) {
				e1.printStackTrace();
			} catch (IOException e1) {
				e1.printStackTrace();
			}
			int skillId = skillMap.get(skillName);
			int categoryId = categoryMap.get(excelContent.getCate_name());
			System.out.println("name:" + excelContent.getUser_name());
			int create_user_id = userMap.get(excelContent.getUser_name());
			try {
				String insert = "INSERT INTO QUESTION(CATE_ID,CATE_NAME,SKILL_ID,SKILL_NAME,QUESTION_CONTENT,CREATE_USER_ID) VALUES (?,?,?,?,?,?)";
				pStatement = conn.prepareStatement(insert);
				pStatement.setInt(1, categoryId);
				pStatement.setString(2, excelContent.getCate_name());
				pStatement.setInt(3, skillId);
				pStatement.setString(4, skillName);
				pStatement.setString(5, excelContent.getQuestion_content());
				pStatement.setInt(6, create_user_id);
				pStatement.executeUpdate();
				String LastId = "select LAST_INSERT_ID()";
				pStatement = conn.prepareStatement(LastId);
				ResultSet r = pStatement.executeQuery(LastId);
				int lastqId = -1;
				if (r.next())
					lastqId = r.getInt(1);
				System.out.println("lastID:" + lastqId);
				// 下面插入到anser表中
				if (excelContent.getCate_name().equals("单选题") || excelContent.getCate_name().equals("多选题")) {
					String anser_nums = excelContent.getAnswer_num();
					String[] nums = anser_nums.split("/");
					int[] is_right = new int[nums.length];
					for (int j = 0; j < is_right.length; j++) {
						if (excelContent.getAnswer_content().contains(nums[j].charAt(0) + "")) {
							is_right[j] = 1;
						}
						insert = "INSERT INTO ANSWER(QUESTION_ID,ANSWER_NUM,ANSWER_CONTENT,IS_RIGHT) VALUES (?,?,?,?)";
						pStatement = conn.prepareStatement(insert);
						pStatement.setInt(1, lastqId);
						pStatement.setString(2, nums[j].charAt(0) + "");
						pStatement.setString(3, nums[j].substring(2));
						pStatement.setInt(4, is_right[j]);
						pStatement.executeUpdate();
					}
				} else if (excelContent.getCate_name().equals("判断题")) {
					insert = "INSERT INTO ANSWER(QUESTION_ID,ANSWER_CONTENT,IS_RIGHT) VALUES (?,?,?)";
					pStatement = conn.prepareStatement(insert);
					pStatement.setInt(1, lastqId);
					pStatement.setString(2, excelContent.getAnswer_content());
					int flag = 1;
					if (excelContent.getAnswer_content().equals("错")) {
						flag = 0;
					}
					pStatement.setInt(3, flag);
					pStatement.executeUpdate();
				} else {
					insert = "INSERT INTO ANSWER(QUESTION_ID,ANSWER_CONTENT,IS_RIGHT) VALUES (?,?,?)";
					pStatement = conn.prepareStatement(insert);
					pStatement.setInt(1, lastqId);
					pStatement.setString(2, excelContent.getAnswer_content());
					int flag = 1;
					pStatement.setInt(3, 1);
					pStatement.executeUpdate();
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		DBUtil.closePreparedStatement(pStatement);
		DBUtil.closeConnection(conn);
	}

	public static Map<String, Integer> getCategoryMap() {
		String sql = "SELECT CATE_NAME,CATE_ID FROM CATEGORY";
		Map<String, Integer> map = new HashMap<String, Integer>();
		try {
			Connection connection = DBUtil.getConnection();
			PreparedStatement pStatement = connection.prepareStatement(sql);
			ResultSet resultSet = pStatement.executeQuery();
			while (resultSet.next()) {
				String cate_name = resultSet.getString(1);
				Integer cate_id = resultSet.getInt(2);
				map.put(cate_name, cate_id);
			}
			DBUtil.closeResultSet(resultSet);
			DBUtil.closePreparedStatement(pStatement);
			DBUtil.closeConnection(connection);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return map;
	}

	public static Map<String, Integer> getSkillMap() {
		String sql = "SELECT SKILL_NAME,SKILL_ID FROM SKILL";
		Map<String, Integer> map = new HashMap<String, Integer>();
		try {
			Connection connection = DBUtil.getConnection();
			PreparedStatement pStatement = connection.prepareStatement(sql);
			ResultSet resultSet = pStatement.executeQuery();
			while (resultSet.next()) {
				String skill_name = resultSet.getString(1);
				Integer skill_id = resultSet.getInt(2);
				map.put(skill_name, skill_id);
			}
			DBUtil.closeResultSet(resultSet);
			DBUtil.closePreparedStatement(pStatement);
			DBUtil.closeConnection(connection);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return map;
	}

	public static Map<String, Integer> getUserMap() {
		String sql = "SELECT USER_NAME,USER_ID FROM USER";
		Map<String, Integer> map = new HashMap<String, Integer>();
		try {
			Connection connection = DBUtil.getConnection();
			PreparedStatement pStatement = connection.prepareStatement(sql);
			ResultSet resultSet = pStatement.executeQuery();
			while (resultSet.next()) {
				String skill_name = resultSet.getString(1);
				Integer skill_id = resultSet.getInt(2);
				map.put(skill_name, skill_id);
			}
			DBUtil.closeResultSet(resultSet);
			DBUtil.closePreparedStatement(pStatement);
			DBUtil.closeConnection(connection);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return map;
	}
}
